package com.example.edupublisher.mapper;

import com.example.edupublisher.bean.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * ClassName: TrafficChannelStatsMapper
 * Package: com.atguigu.edu.publisher.mapper
 * Description:
 *
 * @Author Mr.2
 * @Create 2023/9/9 15:47
 * @Version 1.0
 */
@Mapper
public interface TrafficChannelStatsMapper {
    // 1. 获取各渠道独立访客数
    @Select("SELECT\n" +
            "  ch,\n" +
            "  SUM(uv_ct) uv_ct\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION (par#{date})\n" +
            "GROUP BY ch\n" +
            "ORDER BY uv_ct desc;")
    List<TrafficUvCt> selectUvCt(@Param(value = "date") Integer date);

    // 2. 获取 各渠道会话总数
    @Select("SELECT\n" +
            "  ch,\n" +
            "  SUM(sv_ct) sv_ct\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY ch\n" +
            "ORDER BY sv_ct desc;")
    List<TrafficSvCt> selectSvCt(@Param(value = "date") Integer date);

    // 3. 获取 各渠道会话平均页面浏览数
    @Select("SELECT\n" +
            "  ch,\n" +
            "  CAST(SUM(pv_ct)/SUM(sv_ct) AS DECIMAL(10,2)) pv_per_session\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY ch\n" +
            "ORDER BY pv_per_session desc;")
    List<TrafficPvPerSession> selectPvPerSession(@Param(value = "date") Integer date);

    // 4. 获取 各渠道会话 平均页面访问时长
    @Select("SELECT\n" +
            "ch,\n" +
            "CAST(SUM(dur_sum) / SUM(sv_ct) AS DECIMAL(10,0)) dur_per_session\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY ch\n" +
            "ORDER BY dur_per_session desc;")
    List<TrafficDurPerSession> selectDurPerSession(@Param(value = "date") Integer date);

    // 5. 获取 各渠道 跳出率 -- 有问题, 待会儿在看看
    @Select("SELECT\n" +
            "ch,\n" +
            "SUM(IF(pv_ct=1,1,0)) pv_ct,\n" +
            "SUM(sv_ct) sum_sv_ct,\n" +
            "CAST(SUM(IF(pv_ct=1,1,0)) / SUM(sv_ct) AS DECIMAL(10,4)) uj_rate\n" +
            "FROM dws_traffic_vc_ch_ar_sc_is_new_page_view_window\n" +
            "PARTITION par#{date}\n" +
            "GROUP BY ch\n" +
            "ORDER BY uj_rate desc;")
    List<TrafficUjRate> selectUjRate(@Param(value = "date") Integer date);
}
